﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace IQWebServices
{
    public class OfflineDatabaseCreator
    {
        private Server server = null;
        private string targetDatabase = null;
        private Database database = null;

        public OfflineDatabaseCreator(string connectionString, string targetDatabase)
        {
            // Server connection parameters
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            ServerConnection serverConnection = new ServerConnection(sqlConnection);
            server = new Server(serverConnection);

            // Reference the database
            database = server.Databases[targetDatabase];
        }

        private string GetTransferScript(Database database)
        {
            string delimiter = "\nGO\n\n";

            // Define a Scripter object and set the required scripting options.
            Scripter scripter = new Scripter(server);
            scripter.Options.ScriptDrops = false;
            scripter.Options.WithDependencies = true;
            scripter.Options.Indexes = true;    // To include indexes
            scripter.Options.DriAllConstraints = true;  // to include referential constraints in the script

            StringBuilder sBuilder = new StringBuilder();
            // Iterate though the tables in database and script each one.
            //int count = database.Tables.Count;
            //var tables = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[count];
            //for (int i = 0; i < count; i++) tables[i] = database.Tables[i].Urn;

            //// Generating scripti for table
            //StringCollection scripts = scripter.Script(tables);
            //foreach (string script in scripts) sBuilder.Append(script + delimiter);

            foreach (Table table in database.Tables)
            {
                // check if the table is not a system table
                if (table.IsSystemObject == false)
                {
                    // Generating scripti for table
                    StringCollection scripts = scripter.Script(new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] { table.Urn });
                    foreach (string script in scripts) sBuilder.Append(script + delimiter);
                }
            }

            //var urns = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] { database.Urn };
            //var scripts = scripter.Script(urns);

            var results = sBuilder.ToString();
            return results;
        }

        public string GenerateScripts()
        {
            var results = GetTransferScript(database);
            return results;
        }
    }
}